import { Pool } from 'pg'

// 添加重复任务支持的数据库迁移
export async function addRecurringTasksSupport(pool: Pool): Promise<void> {
  const client = await pool.connect()
  
  try {
    console.log('开始添加重复任务支持...')
    
    // 添加重复任务相关字段到tasks表
    await client.query(`
      ALTER TABLE tasks 
      ADD COLUMN IF NOT EXISTS is_recurring BOOLEAN DEFAULT FALSE,
      ADD COLUMN IF NOT EXISTS recurring_type TEXT,
      ADD COLUMN IF NOT EXISTS recurring_pattern TEXT,
      ADD COLUMN IF NOT EXISTS next_due_date TEXT,
      ADD COLUMN IF NOT EXISTS parent_recurring_id TEXT
    `)
    
    // 创建重复任务模板表
    await client.query(`
      CREATE TABLE IF NOT EXISTS recurring_tasks (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        priority TEXT NOT NULL DEFAULT 'P2',
        category TEXT NOT NULL DEFAULT 'work',
        tags TEXT[],
        notes TEXT,
        user_id TEXT,
        recurring_type TEXT NOT NULL, -- daily, weekly, monthly, yearly
        recurring_pattern TEXT, -- 详细的重复模式描述
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      )
    `)
    
    // 创建重复任务实例表（记录每次生成的具体任务）
    await client.query(`
      CREATE TABLE IF NOT EXISTS recurring_task_instances (
        id TEXT PRIMARY KEY,
        recurring_task_id TEXT NOT NULL,
        task_id TEXT NOT NULL,
        due_date TEXT NOT NULL,
        created_at TEXT NOT NULL,
        FOREIGN KEY (recurring_task_id) REFERENCES recurring_tasks (id) ON DELETE CASCADE,
        FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE
      )
    `)
    
    console.log('重复任务支持添加成功！')
    
  } catch (error) {
    console.error('添加重复任务支持失败:', error)
    throw error
  } finally {
    client.release()
  }
}

// 生成下一个重复任务实例
export async function generateNextRecurringTask(pool: Pool, recurringTaskId: string): Promise<string | null> {
  const client = await pool.connect()
  
  try {
    // 获取重复任务模板
    const recurringTaskResult = await client.query(
      'SELECT * FROM recurring_tasks WHERE id = $1 AND is_active = TRUE',
      [recurringTaskId]
    )
    
    if (recurringTaskResult.rows.length === 0) {
      return null
    }
    
    const recurringTask = recurringTaskResult.rows[0]
    
    // 计算下一个到期日期
    const nextDueDate = calculateNextDueDate(recurringTask.recurring_type)
    
    if (!nextDueDate) {
      return null
    }
    
    // 创建新的任务实例
    const taskId = `task_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`
    const now = new Date().toISOString()
    
    await client.query(`
      INSERT INTO tasks (
        id, title, description, due_date, priority, category, tags, 
        completed, notes, user_id, is_recurring, recurring_type, 
        recurring_pattern, parent_recurring_id, created_at, updated_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
    `, [
      taskId,
      recurringTask.title,
      recurringTask.description,
      nextDueDate,
      recurringTask.priority,
      recurringTask.category,
      recurringTask.tags,
      false,
      recurringTask.notes,
      recurringTask.user_id,
      true,
      recurringTask.recurring_type,
      recurringTask.recurring_pattern,
      recurringTaskId,
      now,
      now
    ])
    
    // 记录重复任务实例
    const instanceId = `instance_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`
    await client.query(`
      INSERT INTO recurring_task_instances (id, recurring_task_id, task_id, due_date, created_at)
      VALUES ($1, $2, $3, $4, $5)
    `, [instanceId, recurringTaskId, taskId, nextDueDate, now])
    
    return taskId
    
  } catch (error) {
    console.error('生成重复任务实例失败:', error)
    return null
  } finally {
    client.release()
  }
}

// 计算下一个到期日期
function calculateNextDueDate(recurringType: string): string | null {
  const now = new Date()
  let nextDate: Date
  
  switch (recurringType) {
    case 'daily':
      nextDate = new Date(now.getTime() + 24 * 60 * 60 * 1000) // 明天
      break
    case 'weekly':
      nextDate = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000) // 下周
      break
    case 'monthly':
      nextDate = new Date(now)
      nextDate.setMonth(nextDate.getMonth() + 1) // 下个月
      break
    case 'yearly':
      nextDate = new Date(now)
      nextDate.setFullYear(nextDate.getFullYear() + 1) // 明年
      break
    default:
      return null
  }
  
  return nextDate.toISOString().split('T')[0] // YYYY-MM-DD格式
}
